package pyip.lib.javalearn.dbbigdata;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.UUID;

public class BigDataOperation {
	public static void main(String[] args) throws SQLException {
		insert();
	}

	private static void insert() throws SQLException {
		System.out.println("begin: " + new Date());

		Connection conn = getConnection();
		boolean oldAutoCommit = conn.getAutoCommit();
		conn.setAutoCommit(false);
		
		PreparedStatement pstmt = conn.prepareStatement("insert into bigdata(id, name, password, locked, updateTime) values "
				+ "(?,?,?,0,?)");

		for(int i = 0; i < 1000; i++) {
			for(int j = 100; j > 0; j--) {
				pstmt.setString(1, getId());
				pstmt.setString(2, "name_" + getId());
				pstmt.setString(3, "pwd_" + getId());
				pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.clearBatch();
			if(i%20 == 0) {
				conn.commit();
			}
			if(i%1000 == 0) {
				System.out.println((i*100) + ", " + new Date());
			}
		}
		conn.commit();
		
		conn.setAutoCommit(oldAutoCommit);
		close(conn, pstmt, null, null);
		System.out.println("end: " + new Date());
	}
	
	private void insertAttach() throws SQLException {
		System.out.println("attach begin: " + new Date());
		
		Connection conn = getConnection();
		boolean oldAutoCommit = conn.getAutoCommit();
		conn.setAutoCommit(false);
		
		Statement stmtCount = conn.createStatement();
		ResultSet rstCount = stmtCount.executeQuery("select count(1) from bigdata");
		int count = 0;
		if(rstCount.next()) {
			count = rstCount.getInt(1);
		}
		close(null, null, stmtCount, rstCount);
		
		PreparedStatement pstmt = conn.prepareStatement("select id from bigdata start ? limit ?");
		PreparedStatement pstmtAttach = conn.prepareStatement("insert into bigdata_attach(id, bigdata_id, a, b, c, d, e, f, updateTime) values "
				+ "(?,?,?,?,?,?,?,?,?)");

		int start = 0, limit = 100;
		ResultSet rstId = null;
		for(int i = 0; i < count; i += limit) {
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			rstId = pstmt.executeQuery();
			while(rstId.next()) {
				String bigdataId = rstId.getString(1);
				Timestamp updateTime = new Timestamp(System.currentTimeMillis());
				for(int k = 0; k < 10; k++) {
					pstmtAttach.setString(1, getId());
					pstmtAttach.setString(2, bigdataId);
					pstmtAttach.setString(3, "a_" + getId());
					pstmtAttach.setString(4, "b_" + getId());
					pstmtAttach.setString(5, "c_" + getId());
					pstmtAttach.setString(6, "d_" + getId());
					pstmtAttach.setString(7, "e_" + getId());
					pstmtAttach.setString(8, "f_" + getId());
					pstmtAttach.setTimestamp(9, updateTime);
					pstmtAttach.addBatch();
				}
			}
			pstmtAttach.executeBatch();
			pstmtAttach.clearBatch();
			// 一万
			if(i*10/10000 == 0) {
				conn.commit();
			}
			// 10万
			if(i*10/100000 == 0) {
				System.out.println("attach: " + (i*10) + ", " + new Date());
			}
			
		}

		conn.commit();
		
		conn.setAutoCommit(oldAutoCommit);
		close(null, pstmt, null, null);
		close(conn, pstmtAttach, null, rstId);
		System.out.println("attach end: " + new Date());
	}
	
	private static String getId() {
		return UUID.randomUUID().toString().replaceAll("-", "");
	}
	
	private static Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		try {
			return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	private static void close(Connection conn, PreparedStatement pstmt, Statement stmt, ResultSet rst) {
		try {
			if (null != conn) {
				conn.close();
			}
			if (null != pstmt) {
				pstmt.close();
			}
			if (null != stmt) {
				stmt.close();
			}
			if (null != rst) {
				rst.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}
